<?php 
function Read_excel($url_file,$data,$start_col=0,$start_row=0,$num_sheet=1,$end_col=0,$end_row=0)
{
	// $url_file: duong dan file
	// $data :  ten danh sach can luu dang array ("ten bien"=> Số cột);
	// $start_col,$start_row: cột và dòng bắt đầu chạy
	// $end_col,$end_row: so dòng và cot ket thuc duyet
	$ci = &get_instance();
	$ci->load->library("excel");
	//  Read your Excel workbook
	$objPHPExcel = PHPExcel_IOFactory::load($url_file);
	$sheet = 0;
	foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) 
	{
		$sheet++;
		if($sheet <= $num_sheet)
		{
			$end_row  	= ($end_row==0)?$worksheet->getHighestRow():$end_row; // e.g. 10
			$highestColumn  	= $worksheet->getHighestColumn(); // e.g 'F'
			
			$end_col =($end_col==0)?PHPExcel_Cell::columnIndexFromString($highestColumn):$end_col;
			
			for ($row = $start_row;$row<= $end_row; ++$row){
				$val = "";$id_employ="";$price = "";
				$data_list="";
				for ($col = $start_col; $col < $end_col; ++ $col){
					$cell = $worksheet->getCellByColumnAndRow($col,$row);
					foreach($data as $name=>$key){
						if($col == $key && $cell->getValue() != "" and  $cell->getValue() != "N/A")
						{
							$data_list[$name] = $cell->getCalculatedValue(); 
						}
					}// ket thuc duyet case
					
				}// ke thuc duyet cot	
				$list[] = $data_list;	 
			}// ket thuc duyet tung dong
		}
	}	
	return array_filter($list);
	
}
function insert_row_colum($data,$row=1,$col=0,$objPHPExcel)
{
	if(!empty($data)){
		foreach($data as $item=>$value){
			$colum = $col;
			if(is_array($value) || is_object($value))
		 	foreach($value as $sub_item=>$sub_value){
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($colum,$row,$sub_value); // stt
				$colum++;
			}
		}
		$row++;
	}
	return $objPHPExcel;
}
function insert_colum($data,$row=1,$col=0,$objPHPExcel){
	if(is_array($data)){
		$colum = $col;
		for($i=0;$i<count($data);$i++){
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($colum,$row,$data[$i]); // stt
			$colum++;
		}
	}
	else if(is_object($data)){
		$colum = $col;
		foreach($data as $name => $value){
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($colum,$row,$value); // stt
			$colum++;
		}	
	}
	return $objPHPExcel;
}
function header_excel($list_title,$symbol_width,$objPHPExcel,$row=1,$num=true,$border=false,$styleArray="")
{
	 /*  
	 	$list_title : danh sach tieu de tung cot vd array(stt,madh...)
		$array_symbol: danh sach ky tu alpha array ("A","B","C",...)
		$symbol_width: danh sach chieu rong array("A"=>30,"B"=>20)
		$objPHPExcel: thu vien excel;
		$row: dong de bat dau chay
		$num: neu bang true mo so dem tren tung cot
		$border: neu bang bang true mo cau hinh mau sac,font chu cho tieu de
		$styleArray: dinh dang font chu mau sac cho $border
	 */	
	 
	 $array_symbol = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG");
	 if(empty($styleArray))
		$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
	 $symbol_orther = array();
	 for($symbol=0;$symbol<count($array_symbol);$symbol++)
	 {
		 $objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		 $objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		 if($symbol < count($list_title)-1)
			 if(!empty($symbol_width))
			 {
				 foreach($symbol_width as $item_symbol=>$value)
				 {
					array_push($symbol_orther,$item_symbol);
					if($item_symbol == $array_symbol[$symbol]){
						$objPHPExcel->getActiveSheet()->getColumnDimension($array_symbol[$symbol])->setWidth($value);
					}
					
				 }
			 }
		 if(!in_array($array_symbol[$symbol],$symbol_orther))
			$objPHPExcel->getActiveSheet()->getColumnDimension($array_symbol[$symbol])->setWidth('20');
			$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ccffcc')))); // mau background
		 $objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getFont()->setBold(true);
		  if($border == true)// border excel header
			$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->applyFromArray($styleArray);
	 }
	 /*  Tên cột */
	 foreach($objPHPExcel->getActiveSheet()->getRowDimensions() as $rd) 
			$rd->setRowHeight(-1); 
	 for($i=0;$i<count($list_title);$i++)
	 {
		 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,$row,$list_title[$i]);
		 if($num == true)
			 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,$row+1,$i);
		
	 }
	 return $objPHPExcel;
}
function excel_textstyle($objPHPExcel,$col,$row,$style_array)
{
	$objPHPExcel->getActiveSheet()->getStyle('B1:E1')->applyFromArray($styleArray);	
	return $objPHPExcel;
}
function excel_textmiddle($objPHPExcel,$col,$row){
	$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}

